﻿---Table NHA_CUNG_CAP---
CREATE PROCEDURE [dbo].[ListNhaCungCap]
AS
begin
SELECT MaNhaCungCap, TenNCC, LoaiDV, DiaChi, Email, DienThoai, UsernameA, PasswordA, WS, TaiKhoan, TinhTrang, HeSoNCC, HeSoGiaBan
FROM NHA_CUNG_CAP
end 
GO

CREATE PROCEDURE [dbo].[InsertNhaCungCap]
@TenNCC nvarchar(50),
@LoaiDV int,
@DiaChi nvarchar(50),
@Email varchar(50),
@DienThoai varchar(13),
@UsernameA varchar(30),
@PasswordA varchar(30),
@WS varchar(150),
@TaiKhoan varchar(30),
@TinhTrang bit,
@HeSoNCC float,
@HeSoGiaBan float
AS
begin
INSERT INTO NHA_CUNG_CAP([TenNCC], [LoaiDV], [DiaChi], [Email] ,[DienThoai], [UsernameA], [PasswordA], [WS], [TaiKhoan], [TinhTrang], [HeSoNCC], [HeSoGiaBan])
VALUES (@TenNCC, @LoaiDV, @DiaChi, @Email, @DienThoai, @UsernameA, @PasswordA, @WS, @TaiKhoan, @TinhTrang, @HeSoNCC, @HeSoGiaBan)
end 
GO

CREATE PROCEDURE [dbo].[UpdateNhaCungCap]
@MaNhaCungCap int,
@TenNCC nvarchar(50),
@LoaiDV int,
@DiaChi nvarchar(50),
@Email varchar(50),
@DienThoai varchar(13),
@UsernameA varchar(30),
@PasswordA varchar(30),
@WS varchar(150),
@TaiKhoan varchar(30),
@TinhTrang bit,
@HeSoNCC float,
@HeSoGiaBan float
AS
begin
UPDATE NHA_CUNG_CAP SET TenNCC = @TenNCC, LoaiDV = @LoaiDV, DiaChi = @DiaChi, Email = @Email,
DienThoai = @DienThoai, UsernameA = @UsernameA, PasswordA = @PasswordA, WS = @WS, TaiKhoan = @TaiKhoan, TinhTrang = @TinhTrang,
HeSoNCC = @HeSoNCC, HeSoGiaBan = @HeSoGiaBan WHERE MaNhaCungCap = @MaNhaCungCap
end 
GO

CREATE PROCEDURE [dbo].[UpdateTTNhaCungCap]
@MaNhaCungCap int,
@TinhTrang bit
AS
begin
UPDATE NHA_CUNG_CAP SET TinhTrang = @TinhTrang WHERE MaNhaCungCap = @MaNhaCungCap
end 

GO

CREATE PROCEDURE [dbo].[DeleteNhaCungCap]
@MaNhaCungCap int
AS
BEGIN
DELETE FROM NHA_CUNG_CAP WHERE MaNhaCungCap = @MaNhaCungCap
END

GO
---Table LOAI_DV---
CREATE PROCEDURE [dbo].[ListLoaiDV]
AS
begin
SELECT MaLoaiDV, TenLoaiDV
FROM LOAI_DV
end 
GO

CREATE PROCEDURE [dbo].[InsertLoaiDV]
@TenLoaiDV nvarchar(50)
AS
begin
INSERT INTO LOAI_DV([TenLoaiDV])
VALUES (@TenLoaiDV)
end 

GO

CREATE PROCEDURE [dbo].[UpdateLoaiDV]
@MaLoaiDV int,
@TenLoaiDV nvarchar(50)
AS
begin
UPDATE LOAI_DV SET [TenLoaiDV] = @TenLoaiDV
WHERE MaLoaiDV = @MaLoaiDV
end 

GO

CREATE PROCEDURE [dbo].[DeleteLoaiDV]
@MaLoaiDV int
AS
begin
DELETE FROM LOAI_DV
WHERE MaLoaiDV = @MaLoaiDV
end
 
GO
---Table KHACH_HANG---

CREATE PROCEDURE [dbo].[ListKhachHang]
AS
begin
SELECT MaKhachHang, TenKH, DiaChi, Email, DienThoai, NgaySinh, SoTheTinDung, Username, Password, TinhTrang
FROM KHACH_HANG
end 

GO

CREATE PROCEDURE [dbo].[InsertKhachHang]
@TenKH nvarchar(50),
@DiaChi nvarchar(50),
@Email varchar(50),
@DienThoai varchar(13),
@NgaySinh date,
@SoTheTinDung varchar(30),
@Username varchar(30),
@Password varchar(40),
@TinhTrang bit
AS
begin
INSERT INTO KHACH_HANG([TenKH], [DiaChi], [Email], [DienThoai], [NgaySinh], [SoTheTinDung], [Username], [Password], [TinhTrang])
VALUES (@TenKH, @DiaChi, @Email, @DienThoai, @NgaySinh, @SoTheTinDung, @Username, @Password, @TinhTrang)
end

GO

CREATE PROCEDURE [dbo].[UpdateKhachHang]
@MaKhachHang int,
@TenKH nvarchar(50),
@DiaChi nvarchar(50),
@Email varchar(50),
@DienThoai varchar(13),
@NgaySinh date,
@SoTheTinDung varchar(30),
@Username varchar(30),
@Password varchar(40),
@TinhTrang bit
AS
begin
UPDATE KHACH_HANG SET TenKH = @TenKH, DiaChi = @DiaChi, Email = @Email, DienThoai = @DienThoai,
NgaySinh = @NgaySinh, SoTheTinDung = @SoTheTinDung, Username = @Username, Password = @Password, TinhTrang = @TinhTrang
WHERE MaKhachHang = @MaKhachHang
end

GO

CREATE PROCEDURE [dbo].[UpdateTTKhachHang]
@MaKhachHang int,
@TinhTrang bit
AS
begin
UPDATE KHACH_HANG SET TinhTrang = @TinhTrang
WHERE MaKhachHang = @MaKhachHang
end

GO

CREATE PROCEDURE [dbo].[UpdateMKKhachHang]
@MaKhachHang int,
@Password varchar(40)
AS
begin
UPDATE KHACH_HANG SET Password = @Password
WHERE MaKhachHang = @MaKhachHang
end

GO

CREATE PROCEDURE [dbo].[DeleteKhachHang]
@MaKhachHang int
AS
BEGIN
	DELETE FROM KHACH_HANG WHERE MaKhachHang = @MaKhachHang
END

GO

CREATE PROCEDURE [dbo].[UpdateKH]
@MaKhachHang int,
@TenKH nvarchar(50),
@DiaChi nvarchar(50),
@Email varchar(50),
@DienThoai varchar(13),
@NgaySinh date,
@SoTheTinDung varchar(30),
@Username varchar(30),
@Password varchar(40),
@TinhTrang bit
AS
begin
if @Password = ''
	UPDATE KHACH_HANG SET TenKH = @TenKH, DiaChi = @DiaChi, Email = @Email, DienThoai = @DienThoai,
	NgaySinh = @NgaySinh, SoTheTinDung = @SoTheTinDung, Username = @Username, TinhTrang = @TinhTrang
	WHERE MaKhachHang = @MaKhachHang
else
	UPDATE KHACH_HANG SET TenKH = @TenKH, DiaChi = @DiaChi, Email = @Email, DienThoai = @DienThoai,
NgaySinh = @NgaySinh, SoTheTinDung = @SoTheTinDung, Username = @Username, Password = @Password, TinhTrang = @TinhTrang
WHERE MaKhachHang = @MaKhachHang
end
GO

CREATE PROCEDURE dbo.UpdatePass
	@MaKH int,
	@MatKhau varchar(40)
AS
BEGIN
	UPDATE KHACH_HANG SET Password = @MatKhau WHERE MaKhachHang = @MaKH
END

GO

CREATE PROCEDURE dbo.CheckUsername
	@TenDangNhap varchar(30),
	@returnVal int out
AS
BEGIN
	DECLARE @countUsername int
	SET @returnVal = null
	SELECT @countUsername = COUNT(*) FROM KHACH_HANG
	WHERE @TenDangNhap = Username
	
	if @countUsername !=0
		SET @returnVal = 1
	else
		SET @returnVal = 0
END

GO

---Table NHAN_VIEN---

CREATE PROCEDURE [dbo].[ListNhanVien]
AS
begin
SELECT MaNV, TenNV, ChucVu, Username, Password, TinhTrang, LoaiNhanVien
FROM NHAN_VIEN
end 
GO

CREATE PROCEDURE [dbo].[InsertNhanVien]
@TenNV nvarchar(50),
@ChucVu nvarchar(50),
@Username varchar(30),
@Password varchar(40),
@TinhTrang bit,
@LoaiNhanVien nvarchar(50)
AS
begin
INSERT INTO NHAN_VIEN([TenNV], [ChucVu], [Username], [Password], [TinhTrang], [LoaiNhanVien])
VALUES (@TenNV, @ChucVu, @Username, @Password, @TinhTrang, @LoaiNhanVien)
end 

GO
CREATE PROCEDURE [dbo].[UpdateNhanVien]
@MaNV int,
@TenNV nvarchar(50),
@ChucVu nvarchar(50),
@Username varchar(30),
@Password varchar(40),
@TinhTrang bit,
@LoaiNhanVien nvarchar(50)
AS
begin
UPDATE NHAN_VIEN SET [TenNV] = @TenNV, [ChucVu] = @ChucVu, [Username]=@Username, [Password]=@Password, [TinhTrang]=@TinhTrang, [LoaiNhanVien] = @LoaiNhanVien
WHERE [MaNV] = @MaNV
end 

GO

CREATE PROCEDURE [dbo].[DeleteNhanVien]
@MaNV int
AS
begin
DELETE FROM NHAN_VIEN
WHERE [MaNV] = @MaNV
end
GO
---Table HOA_DON_NCC---

CREATE PROCEDURE [dbo].[ListHoaDonNCC]
AS
begin
SELECT MaHoaDon, MaTaiKhoan, NgayLap, NhaCC, TongTien, TinhTrang
FROM HOA_DON_NCC
end 
GO
CREATE PROCEDURE [dbo].[InsertHoaDonNCC]
@MaTaiKhoan int,
@NgayLap date,
@NhaCC int,
@TongTien int,
@TinhTrang bit
AS
begin
INSERT INTO HOA_DON_NCC([MaTaiKhoan], [NgayLap], [NhaCC], [TongTien], [TinhTrang])
VALUES (@MaTaiKhoan, @NgayLap, @NhaCC, @TongTien, @TinhTrang)
end 
GO
---Table HOA_DON_GIAO_DICH---
CREATE PROCEDURE [dbo].[ListHoaDonGiaoDich]
AS
begin
SELECT MaGiaoDich, MaTK, NgayLap, NhaCC, HD_NCC, TongTien, TinhTrang, HoaDonNCC
FROM HOA_DON_GIAO_DICH
end 
GO

CREATE PROCEDURE [dbo].[InsertHoaDonGiaoDich]
@MaTK int,
@NgayLap date,
@NhaCC int,
@HD_NCC varchar(25),
@TongTien int,
@TinhTrang bit,
@HoaDonNCC int
AS
begin
INSERT INTO HOA_DON_GIAO_DICH([MaTK], [NgayLap], [NhaCC], [HD_NCC], [TongTien], [TinhTrang], [HoaDonNCC])
VALUES (@MaTK, @NgayLap, @NhaCC, @HD_NCC, @TongTien, @TinhTrang, @HoaDonNCC)
end 
GO

CREATE PROCEDURE dbo.GetHoaDonGD_Travel
	@TenDangNhap varchar(30)
AS
BEGIN
	SELECT MaGiaoDich,TenNCC, NCC.DiaChi AS DiaChi, NgayLap, TongTien, HDGD.TinhTrang AS TinhTrang, Username
	FROM KHACH_HANG KH
	JOIN HOA_DON_GIAO_DICH HDGD ON KH.MaKhachHang =  HDGD.MaTK
	JOIN NHA_CUNG_CAP NCC ON NCC.MaNhaCungCap = HDGD.NhaCC
	JOIN LOAI_DV LDV ON NCC.LoaiDV = MaLoaiDV
	WHERE KH.Username = @TenDangNhap AND TenLoaiDV = 'Travel'
END
GO

CREATE PROCEDURE dbo.GetHoaDonGD_Hotel
	@TenDangNhap varchar(30)
AS
BEGIN
	SELECT MaGiaoDich,TenNCC, NCC.DiaChi AS DiaChi, NgayLap, TongTien, HDGD.TinhTrang AS TinhTrang, Username
	FROM KHACH_HANG KH
	JOIN HOA_DON_GIAO_DICH HDGD ON KH.MaKhachHang =  HDGD.MaTK
	JOIN NHA_CUNG_CAP NCC ON NCC.MaNhaCungCap = HDGD.NhaCC
	JOIN LOAI_DV LDV ON NCC.LoaiDV = MaLoaiDV
	WHERE KH.Username = @TenDangNhap AND TenLoaiDV = 'Hotel'
END
GO

CREATE PROCEDURE dbo.GetHoaDonGD_Transport
	@TenDangNhap varchar(30)
AS
BEGIN
	SELECT MaGiaoDich,TenNCC, NCC.DiaChi AS DiaChi, NgayLap, TongTien, HDGD.TinhTrang AS TinhTrang, Username
	FROM KHACH_HANG KH
	JOIN HOA_DON_GIAO_DICH HDGD ON KH.MaKhachHang =  HDGD.MaTK
	JOIN NHA_CUNG_CAP NCC ON NCC.MaNhaCungCap = HDGD.NhaCC
	JOIN LOAI_DV LDV ON NCC.LoaiDV = MaLoaiDV
	WHERE KH.Username = @TenDangNhap AND TenLoaiDV = 'Transport'
END
GO

CREATE PROCEDURE dbo.GetHoaDonGD_Airplane
	@TenDangNhap varchar(30)
AS
BEGIN
	SELECT MaGiaoDich,TenNCC, NCC.DiaChi AS DiaChi, NgayLap, TongTien, HDGD.TinhTrang AS TinhTrang, Username
	FROM KHACH_HANG KH
	JOIN HOA_DON_GIAO_DICH HDGD ON KH.MaKhachHang =  HDGD.MaTK
	JOIN NHA_CUNG_CAP NCC ON NCC.MaNhaCungCap = HDGD.NhaCC
	JOIN LOAI_DV LDV ON NCC.LoaiDV = MaLoaiDV
	WHERE KH.Username = @TenDangNhap AND TenLoaiDV = 'Airplane'
END
GO
--Đăng nhập khách hàng----

CREATE procedure [dbo].[spKhachHang_DangNhap]
	@TenDangNhap varchar(30),
	@MatKhau varchar(40),
	@returnVal int out
AS
begin
	Declare @countDangNhap int, @countMatKhau int, @TrangThai bit
	--Kiểm tra ten đăng nhập
	select @countDangNhap = COUNT(*) From KHACH_HANG Where Username = @TenDangNhap
	if @countDangNhap=0
		set @returnVal=1
	else
		BEGIN
			select @countMatKhau=COUNT(*) From KHACH_HANG Where Username=@TenDangNhap AND Password=@MatKhau
			if @countMatKhau=0
				SET @returnVal=2
			else
				BEGIN
					select @TrangThai=Tinhtrang From KHACH_HANG Where Username=@TenDangNhap
					if @TrangThai=0
						Set @returnVal=3
					else
						set @returnVal=0
				END
		END
end
GO
--Đăng nhập nhân viên---

CREATE PROCEDURE dbo.spNhanVien_DangNhap
	@TenDangNhap varchar(30),
	@MatKhau varchar(40),
	@returnVal int out
AS
begin
	Declare @countDangNhap int, @countMatKhau int, @TrangThai bit
	SET @returnVal = null
	--Kiểm tra ten đăng nhập
	select @countDangNhap = COUNT(*) From NHAN_VIEN Where Username = @TenDangNhap
	if @countDangNhap=0
		set @returnVal=1
	else
		BEGIN
			select @countMatKhau=COUNT(*) From NHAN_VIEN Where Username=@TenDangNhap AND Password=@MatKhau
			if @countMatKhau=0
				SET @returnVal=2
			else
				BEGIN
					select @TrangThai=Tinhtrang From NHAN_VIEN Where Username=@TenDangNhap
					if @TrangThai=0
						Set @returnVal=3
					else
						set @returnVal=0
				END
		END
end
